using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

namespace Sys.DataAccess
{
    public class SqlDatabaseCommand : DatabaseCommand
    {
        private SqlDbType ConvertToSqlDbType(DbType dbType)
        {
            switch (dbType)
            {
                case DbType.Int64:
                    return SqlDbType.BigInt;
                case DbType.Int32:
                    return SqlDbType.Int;
                case DbType.Int16:
                    return SqlDbType.SmallInt;
                case DbType.Boolean:
                    return SqlDbType.Bit;
                case DbType.Object:
                    return SqlDbType.Variant;
                case DbType.StringFixedLength:
                    return SqlDbType.NVarChar;
                case DbType.String:
                    return SqlDbType.Text;       
                default:
                    return SqlDbType.Variant;
            }
        }

        private List<SqlParameter> parameters = new List<SqlParameter>();

        #region Constructor
        public SqlDatabaseCommand(string commandText, CommandType commandType, string connectionString)
            : this(commandText, commandType, false, connectionString) {}
        public SqlDatabaseCommand(string commandText, CommandType commandType, bool resultStructureIsDynamic, string connectionString)
            : base(commandText, commandType, resultStructureIsDynamic, connectionString) { }
        #endregion

        public override void AddInParameter(string parameterName, object value)
        {
            parameters.Add(new SqlParameter(parameterName, value));
        }

        public override void AddInParameter(string parameterName, DbType paramType, object value)
        {
            SqlParameter param = new SqlParameter(parameterName, paramType);
            param.Value = value;
            parameters.Add(param);
        }

        public override void AddOutputParameter(string parameterName, DbType dbType)
        {
            SqlParameter param = new SqlParameter(parameterName, ConvertToSqlDbType(dbType));
            param.Direction = ParameterDirection.Output;
            parameters.Add(param);
        }

        public override void AddOutputParameter(string parameterName, DbType dbType, int size)
        {
            SqlParameter param = new SqlParameter(parameterName, ConvertToSqlDbType(dbType), size);
            param.Direction = ParameterDirection.Output;
            parameters.Add(param);
        }

        public override object GetParameterValue(string paramName)
        {
            foreach (SqlParameter param in parameters)
            {
                if (param.ParameterName == paramName) return param.Value;
            }
            return null;
        }

        public override int ExecuteNonQuery()
        {           
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            foreach (SqlParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }

        public override object ExecuteScalar()
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            foreach (SqlParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            conn.Open();
            object result = cmd.ExecuteScalar();
            conn.Close();
            return result;
        }

        public override DataTable GetResultsAsTable()
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            foreach (SqlParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable results = new DataTable();
            conn.Open();
            adapter.Fill(results);
            conn.Close();
            return results;
        }

        public override DataSet GetResultsAsDataSet()
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            foreach (SqlParameter param in parameters)
            {
                cmd.Parameters.Add(param);
            }
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet results = new DataSet();
            conn.Open();
            adapter.Fill(results);
            conn.Close();
            return results;
        }
    }
}
